﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTOQLDV;
using System.Data;
using System.Data.OleDb;

namespace DAOQLDV
{
    public class DAOQuanHuyen
    {
        public static List<DTOQuanHuyen> LayDanhSachQuanHuyen()
        {
            List<DTOQuanHuyen> dsQuanHuyen = new List<DTOQuanHuyen>();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From QUANHUYEN";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                while (dr.Read())
                {
                    DTOQuanHuyen dtoQH = new DTOQuanHuyen();
                    dtoQH.MaQuanHuyen = (int)dr["MaQuanHuyen"];
                    dtoQH.TenQuanHuyen = (String)dr["TenQuanHuyen"];
                    dsQuanHuyen.Add(dtoQH);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dsQuanHuyen;
        }
        public static DTOQuanHuyen LayThongTinQuanHuyen(String tenQuanHuyen)
        {
            DTOQuanHuyen dtoQuanHuyen = new DTOQuanHuyen();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From QUANHUYEN Where TenQuanHuyen like '" + tenQuanHuyen + "'";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                if (dr.Read())
                {
                    dtoQuanHuyen.MaQuanHuyen = (int)dr["MaQuanHuyen"];
                    dtoQuanHuyen.TenQuanHuyen = dr["TenQuanHuyen"].ToString();
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dtoQuanHuyen;
        }        
        public static bool ThemQuanHuyen(DTOQuanHuyen dtoQH)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoQH.TenQuanHuyen.Equals(""))
                {
                    da.Open();
                    da.BeginTran();
                    String sql = "Insert Into QUANHUYEN(TenQuanHuyen) Values ('" + dtoQH.TenQuanHuyen + "')";
                    da.ExecuteNonQuery(sql);
                    sql = "Select @@IDENTITY";
                    dtoQH.MaQuanHuyen = (int)da.ExecuteScalar(sql);
                    DAOTuKhoaQuanHuyen.ThemTuKhoaQuanHuyen(dtoQH, da);
                    da.CommitTran();
                }
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            return kq;
        }        
        public static bool CapNhatQuanHuyen(DTOQuanHuyen dtoQH)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoQH.TenQuanHuyen.Equals(""))
                {
                    da.Open();
                    String sql = "Update QUANHUYEN Set TenQuanHuyen = '" + dtoQH.TenQuanHuyen + "' Where MaTenQuanHuyen = " + dtoQH.MaQuanHuyen;
                    da.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }        
        public static bool XoaQuanHuyen(DTOQuanHuyen dtoQH)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                if (!dtoQH.TenQuanHuyen.Equals(""))
                {
                    da.Open();
                    String sql = "Delete From QUANHUYEN Where MaTenQuanHuyen = " + dtoQH.MaQuanHuyen;
                    da.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
        public static int LayMaQuanHuyen(String tenQuanHuyen)
        {
            int kq = 0;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select MaQuanHuyen From QUANHUYEN Where TenQuanHuyen Like '" + tenQuanHuyen + "'";
                kq = int.Parse("0" + da.ExecuteScalar(sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
    }
}
